﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
using System.Runtime.Serialization;
using System.IO;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Configuration;

namespace MyCommerce.Business.GeneralParameters
{
    public static class ReportManager
    {
        public static DataTable GetStoreReport()
        {
            string query = @"SELECT s.IdStore, s.Code, s.[Address], s.Name, s.IsCentralStore, s.IsActive,
                           s.Phone,EmployeePhone=e.Phone,
                           e.CellPhone, EmployeeAddress=e.[Address]
                           , e.FamilyName2,e.FamilyName1,EmployeeName=e.Name
                            FROM Store.Store s
                            INNER JOIN [Security].Employee e ON e.IdEmployee = s.IdEmployee";

            return ExecuteSqlReportQuery(query);
        }

        public static DataTable GetDealerStoreReport()
        {
            string query = @"SELECT ds.IdDealerStore, ds.Code, ds.Name, ds.[Address], ds.IsActive, ds.Phone,EmployeePhone=e.Phone,
                           e.CellPhone, EmployeeAddress=e.[Address]
                           , e.FamilyName2,e.FamilyName1,EmployeeName=e.Name,StoreCode=s.Code, StoreName=s.Name,
                           StoreAddress=s.[Address]
                            FROM Store.DealerStore ds
                            INNER JOIN Store.Store s ON s.IdStore = ds.IdStore
                            INNER JOIN [Security].Employee e ON e.IdEmployee = ds.IdEmployee";

            return ExecuteSqlReportQuery(query);
        }

        public static DataTable GetSupplierReport(IList<long> idsCountriesSelected)
        {
            string queryWhere = string.Empty;
            if (idsCountriesSelected.Count > 0)
            {
                string ids = string.Empty;
                foreach (long item in idsCountriesSelected)
                {
                    if (string.IsNullOrEmpty(ids))
                        ids = item.ToString();
                    else
                        ids = ids + ", " + item.ToString();
                }
                queryWhere = @" WHERE s.IdSupplier in (" + ids  + @")";
            }

            string query = @"SELECT s.IdSupplier, s.[Name], s.[Address], s.Email, s.Phone, s.PersonContactName,
                                   s.IdCountry, c.NameCountry
                            FROM Store.Supplier s
                            LEFT JOIN Common.Country c ON c.IdCountry = s.IdCountry ";

            return ExecuteSqlReportQuery(query + queryWhere);
        }

        public static DataTable GetConveyorReport()
        {
            string query = @"SELECT c.IdConveyor, c.Code, c.FullName, c.[Address], c.Phone, c.Observation
                            FROM Store.Conveyor c";

            return ExecuteSqlReportQuery(query);
        }

        public static DataTable GetProductReport(IList<long> idsMarkSelected)
        {
            string queryWhere = string.Empty;
            string idsMark = GetStringFromList(idsMarkSelected);
            if (!string.IsNullOrEmpty(idsMark))
                queryWhere = @" WHERE p.IdcMark in (" + idsMark + @")";

            string query = @"SELECT p.IdProduct, p.IdcCategory, p.IdcMark, p.IdcProductType, p.Model,
                                   p.[Description], p.OtherDescription, p.ProductLink, p.WithGuarantee, 
                                   cat.[Name] AS Category, ma.[Name] AS Mark, prot.[Name] AS ProductType
                              FROM Store.Product p
                            INNER JOIN Common.Classifier cat ON cat.IdClassifier = p.IdcCategory
                            INNER JOIN Common.Classifier ma ON ma.IdClassifier = p.IdcMark
                            INNER JOIN Common.Classifier prot ON prot.IdClassifier = p.IdcProductType";

            return ExecuteSqlReportQuery(query + queryWhere);
        }

        public static DataTable GetProductPriceReport(IList<long> idsMarkSelected)
        {
            string queryWhere = string.Empty;
            string idsMark = GetStringFromList(idsMarkSelected);
            if (!string.IsNullOrEmpty(idsMark))
                queryWhere = @" AND p.IdcMark in (" + idsMark + @")";

            string query = @"SELECT pp.IdProductPrice, pp.IdProduct, pp.PurchasePrice, pp.SalePrice,
                                   pp.IdcMoney, pp.IdSupplier, pp.IsCurrentPrice, pp.RegisterDate,
                                   pp.OtherPurchasePrice, p.Model, cl.[Name] AS [MONEY], s.[Name] AS Supplier,
                                   cat.[Name] AS Category, ma.[Name] AS Mark, prot.[Name] AS ProductType 
                              FROM Store.ProductPrice pp
                            INNER JOIN Store.Product p ON p.IdProduct = pp.IdProduct
                            INNER JOIN Common.Classifier cl ON cl.IdClassifier = pp.IdcMoney
                            LEFT JOIN Store.Supplier s ON s.IdSupplier = pp.IdSupplier
                            INNER JOIN Common.Classifier cat ON cat.IdClassifier = p.IdcCategory
                            INNER JOIN Common.Classifier ma ON ma.IdClassifier = p.IdcMark
                            INNER JOIN Common.Classifier prot ON prot.IdClassifier = p.IdcProductType
                            WHERE pp.IsCurrentPrice = 1";

            return ExecuteSqlReportQuery(query + queryWhere);
        }

        public static DataTable ExecuteSqlReportQuery(string queryToExecute)
        {
            DataTable dtGenerado = new DataTable();

            string cnnString = ConfigurationManager.ConnectionStrings["MyCommerceEntities"].ConnectionString;

            string[] contections = cnnString.Split(new string[] { @"provider connection string=" }, StringSplitOptions.None);

            cnnString = "";
            for (int index = 1; index < contections.Length; index++)
            {
                cnnString = contections[index];
            }

            cnnString = cnnString.Replace(@"""", "");

            ///Buscamos en la BD todos comandos que no estan en el archivo XML
            using (SqlConnection vCnn = new SqlConnection(cnnString))
            {
                try
                {
                    SqlDataAdapter daQuery = new SqlDataAdapter(queryToExecute, vCnn);
                    daQuery.Fill(dtGenerado);

                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }


            return dtGenerado;
        }

        private static string GetStringFromList(IList<long> idsSelected)
        {
            string ids = string.Empty;

            if (idsSelected.Count > 0)
            {
                foreach (long item in idsSelected)
                {
                    if (string.IsNullOrEmpty(ids))
                        ids = item.ToString();
                    else
                        ids = ids + ", " + item.ToString();
                }
            }

            return ids;
        }
    }
}
